﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Utils;
using DataHelper;
namespace DAL
{
    public class EmpSetsDao
    {

        EmployeeDao employeeDao = new EmployeeDao();
        public string GetSetsID(string empCode)
        {
            return SQLHelper.ExecuteSqlGetNumber("Select SetsID from Wage_EmpSets where EmpCode='" + empCode + "'");
        }

        public DataTable GetOneInfo(string empCode)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from Wage_EmpSets where EmpCode='" + empCode + "'");
        }



        public bool DeleteOneInfo(string empCode)
        {
            return SQLHelper.ExecuteSql("delete from Wage_EmpSets where EmpCode='" + empCode + "'");
        }
        public bool DeleteDeptInfo(string deptID, string setsID)
        {
            if (deptID == "0")
                return SQLHelper.ExecuteSql("delete from Wage_EmpSets where SetsID=" + setsID + " and EmpCode in (select EmpCode From Admin_Employee where StateID<>3 )");
            else
                return SQLHelper.ExecuteSql("delete from Wage_EmpSets where SetsID=" + setsID + " and EmpCode in (select EmpCode From Admin_Employee where DeptID=" + deptID + " and StateID<>3 )");
        }

        public bool IsExistEmpCode(string empCode)
        {
            DataTable dt = GetOneInfo(empCode);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }
        //显示没有帐套的员工，主要用于新增加员工
        //2011-7-18 增加当前月入职，当前月离职的员工
        public DataTable GetLists()
        {
            int mm = System.DateTime.Now.Month;
            int yy = System.DateTime.Now.Year;
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append("[DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [JoinTime],[SalaryState],[LeaveTime] ");
            strFilter.Append(" From [View_Emp] Where (StateID<>3 Or   (DATEDIFF(month, LeaveTime, GETDATE()) <= 1)) and EmpCode not in (Select EmpCode from Wage_EmpSets)");
            strFilter.Append(" Order by DeptID ASC, EmpCode ASC");
            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());

        }

        //查询显示
        public DataTable GetLists(string empCode, string deptID, string setsID)
        {
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append("[DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [JoinTime],[SalaryState],[SetsName],[SetsID] ");
            strFilter.Append(" From [View_EmpSets] Where SetsID=" + setsID + "  ");
            if (!string.IsNullOrWhiteSpace(empCode)) strFilter.Append("and (EmpCode like '" + empCode + "%' OR EmpName like '%" + empCode + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0") strFilter.Append(" and DeptID=" + Int32.Parse(deptID));

            }
            strFilter.Append(" Order by DeptID ASC, EmpCode ASC");

            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
        }
        //给单个员工或部门设置帐套
        public bool ConfigSets(string empCode, string deptID, string setsID)
        {
            bool flag = true;
            if (!string.IsNullOrWhiteSpace(empCode))
            {
                if (!IsExistEmpCode(empCode) && employeeDao.IsExistEmpCode(empCode))
                {
                    flag = SQLHelper.ExecuteSql("insert into Wage_EmpSets (EmpCode,SetsID,Statu) values ('" + empCode + "'," + setsID + ",'Y')");
                }
                else
                    flag = false;
            }
            else
            {
                if ((deptID == "0" || deptID == "") && setsID != "0")
                    flag = SQLHelper.ExecuteSql("insert into Wage_EmpSets (EmpCode,SetsID) select EmpCode," + setsID + " from Admin_Employee  where  stateid<>3 and EmpCode not in (select empcode from wage_EmpSets)");
                else if (deptID != "0" && setsID != "0")
                    flag = SQLHelper.ExecuteSql("insert into Wage_EmpSets (EmpCode,SetsID) select EmpCode," + setsID + " from Admin_Employee  where deptid=" + deptID + " and stateid<>3 and EmpCode not in (select empcode from wage_EmpSets)");
                else
                    flag = false;

            }
            return flag;
        }

        //给一组人设置帐套
        public bool ConfigSets(ArrayList empCodeArray, string setsID)
        {
            bool flag = true;
            if (empCodeArray.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (object empCode in empCodeArray)
                {
                    string code = empCode.ToString();
                    if (!IsExistEmpCode(code) && employeeDao.IsExistEmpCode(code))
                    {
                        stringBuilder.Append("insert into Wage_EmpSets (EmpCode,SetsID,Statu) values ('" + code + "'," + setsID + ",'Y');");
                    }
                    else
                        continue;
                }
                string strSQL = stringBuilder.ToString();
                if (!string.IsNullOrEmpty(strSQL))
                    flag = SQLHelper.ExecuteSql(stringBuilder.ToString());
                else
                    flag = false;
            }
            else
            {
                flag = false;

            }
            return flag;
        }

        
    }
}
